# @Time : 2024/12/24 22:32
# @Author : Fioman 
# @Phone : 13149920693
"""
pymysql的封装操作类,使用with语句操作mysql
"""
from datetime import datetime
from timeit import default_timer
from typing import Tuple, List, Dict

import pymysql
import pymysql.cursors
from dbutils.pooled_db import PooledDB

from eap_tool.eap_logger import debug_eap


class MysqlConfig(object):
    """
    mysql数据库配置,换了项目之后,只需要修改dbName即可
    """

    def __init__(self):
        self.dbName = "xfjw01"
        self.host = 'localhost'
        self.port = 3306
        self.user = "root"
        self.password = "123456"

        self.charset = 'utf8mb4'
        self.minCached = 10  # 连接池中空闲连接的初始数量
        self.maxCached = 20  # 连接池中空闲连接的最大数量
        self.maxShared = 20  # 共享连接的最大数量
        self.maxConnection = 100  # 创建连接池的最大数量

        # 超过最大连接数量时候的表现,为True等待连接数量下降,为False直接报错处理
        self.blocking = True
        # 单个连接的最大重复使用次数
        self.maxUsage = 100
        # 可选的sql命令列表,用于准备会话,比如["set statestyle to ...","set time zone"]
        self.setSession = None
        # 当连接返回到池中时,应该如何重置连接.[False或者None 回滚到以begin()开始的事务,True,
        # 为了安全起见总是发出回滚]
        self.reset = True


# 全局的连接池对象,初始状态为None,使用的时候只去配置一次
globalPoolConn = None


class MysqlPoolConn(object):
    """
    数据库连接池管理类
    """
    _pool = None

    def __init__(self, config):
        if MysqlPoolConn._pool is None:
            MysqlPoolConn._pool = PooledDB(creator=pymysql,
                                           mincached=config.minCached,
                                           maxcached=config.maxCached,
                                           blocking=config.blocking,
                                           maxusage=config.maxUsage,
                                           setsession=config.setSession,
                                           charset=config.charset,
                                           host=config.host,
                                           port=config.port,
                                           database=config.dbName,
                                           user=config.user,
                                           password=config.password)

    @classmethod
    def get_conn(cls):
        """
        数据库连接池中获取连接
        :return:
        """
        return cls._pool.connection()


class MysqlOperator(object):
    """
    mysql数据库的操作类
    """

    def __init__(self, commit=True, logTime=False, logLabel="总用时"):
        """
        :param commit: 是否在最后提交事务(设置为False的时候方便单元测试)
        :param logTime: 是否打印程序运行总时间
        :param logLabel: 自定义log的文字
        """
        self.logTime = logTime
        self.commit = commit
        self.logLabel = logLabel

    def __enter__(self):
        global globalPoolConn
        # 如果需要统计时间
        if self.logTime:
            self.start = default_timer()
        if globalPoolConn is None:
            globalPoolConn = MysqlPoolConn(MysqlConfig())
        self.conn = globalPoolConn.get_conn()
        # 获取游标,指定返回的结果是字典类型,可以通过字段属性名直接访问
        self.cursor = self.conn.cursor(pymysql.cursors.DictCursor)
        return self

    def __exit__(self, excType, excValue, traceBack):
        # 提交事务
        if self.commit:
            self.conn.commit()

        # 退出是关闭连接和conn
        self.cursor.close()
        self.conn.close()
        if self.logTime:
            timeUsed = default_timer() - self.start
            debug_eap(f"{self.logLabel}: {timeUsed:.3f}")

    def create_table(self, createSql: str):
        """
        创建数据库
        :param createSql: 创建sql的语句,这里不捕获异常的原因是这里一般出现异常都是语句那里有错误
                                        所以捕获异常的地方弄到上层去,这样修改就在上层去修改,不用跑到这里来
        :return:
        """
        self.cursor.execute(createSql)

    def delete_data(self, tableName: str) -> int:
        """
        清除表数据
        :param tableName:
        :return:
        """
        deleteSql = f"delete from {tableName}"
        self.cursor.execute(deleteSql)
        deleteCount = self.cursor.rowcount
        return deleteCount

    def insert_data_one(self, sql: str, params: Tuple = None):
        """
        插入语句
        :param sql:
        :param params:插入的参数
        :return:
        """
        self.cursor.execute(sql, params)

    def get_count(self, tableName: str):
        """
        统计某个表中的数据条目的个数
        :param tableName:
        :return:
        """
        self.cursor.execute(f"select count(*)  as total from {tableName}")
        count = self.cursor.fetchone()["total"]
        return count

    def fetch_one(self, sql, params=None):
        """
        获取一条数据
        :param sql:
        :param params:
        :return:获取一条数据,如果无数据,返回None
        """
        self.cursor.execute(sql, params)
        return self.cursor.fetchone()

    def fetch_all(self, tableName, params=None) -> List[Dict]:
        """
        获取表中所有的数据
        :param tableName:
        :param params:
        :return:
        """
        sql = f"select * from {tableName}"
        self.cursor.execute(sql, params)
        res = self.cursor.fetchall()
        dataNew = []
        # 因为这些数据可能要进行json序列化发送到前端,所以要将datetime格式的数据转换为str
        for row in res:
            if "CREATETIME" in row and isinstance(row["CREATETIME"], datetime):
                row["CREATETIME"] = row["CREATETIME"].strftime("%Y-%m-%d %H:%M:%S")
            if "EMPTIME" in row and isinstance(row["EMPTIME"], datetime):
                row["EMPTIME"] = row["EMPTIME"].strftime("%Y-%m-%d %H:%M:%S")
            dataNew.append(row)
        return dataNew

    def delete_data_by_field_val(self, tableName, fieldName, fieldVal, deleteMany=True) -> int:
        """
        根据字段的值去删除对应的记录,会删除多条满足条件的记录.
        :param tableName:
        :param fieldName:
        :param fieldVal:
        :param deleteMany:是否删除多条记录,默认是
        :return: 返回删除了多少条记录
        """
        if deleteMany:
            sql = f"delete from {tableName} where {fieldName}='{fieldVal}'"
        else:
            sql = f"delete from {tableName} where {fieldName}='{fieldVal}' Limit 1"
        self.cursor.execute(sql)
        deleteCount = self.cursor.rowcount
        return deleteCount

    def copy_table(self, tableFrom, tableTo):
        """
        将表从一个表复制到另外一表
        :param tableFrom: 要复制的原表(有内容的表)
        :param tableTo: 复制的新表
        :return:
        """
        sql = f"insert into {tableFrom} select * from {tableTo}"
        self.cursor.execute(sql)


if __name__ == '__main__':
    with MysqlOperator() as sp:
        sqlTest = "Create Table If Not Exists PC_DATA_CRAFT" + "(" + \
                  "ID BIGINT PRIMARY KEY AUTO_INCREMENT," + \
                  "CONTAINER VARCHAR(50)," + \
                  "ITEMCODE VARCHAR(30)," + \
                  "ITEMDES VARCHAR(100)," + \
                  "UOM VARCHAR(30)," + \
                  "ITEMVALUE VARCHAR(200)," + \
                  "ISOK VARCHAR(1)," + \
                  "GROUPNUM BIGINT," + \
                  "CREATETIME DATETIME" + \
                  ")"

        sp.create_table(sqlTest)
